﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace QLNS_Server
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "Service_QLNS" in both code and config file together.
    public class Service_QLNS : IService_QLNS
    {
        //Định nghĩa hàm lấy danh sách tôn giáo.............................................................................................
        public List<TON_GIAO> getTG()
        {
            DataTable TG = new DataTable();
            sqlHelper.Fill(TG, "select * from TON_GIAO");
            List<TON_GIAO> ListTG = new List<TON_GIAO>();
            foreach (DataRow dr in TG.Rows)
            {
                TON_GIAO tg = new TON_GIAO();
                tg.Ma_TG = Convert.ToInt32(dr[0]);
                tg.TenTG = dr[1].ToString();
                ListTG.Add(tg);
            }
            return ListTG;
            
        }
        // Định nghĩa hàm thêm 1 tôn giáo vào danh sách
        public int addTG(TON_GIAO tg)
        {
           
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TG", SqlDbType.Int);
            pr[0].Value = tg.Ma_TG;
            pr[1] = new SqlParameter("@TenTG", SqlDbType.NVarChar);
            pr[1].Value = tg.TenTG;
            kq = sqlHelper.ExecuteNonQuery("insert TON_GIAO(Ma_TG, TenTG) values (@Ma_TG, @TenTG)", pr);
            return kq;
        }

        //Định nghĩa hàm sửa 1 tôn giáo
        public void setTG(TON_GIAO tg)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TG", SqlDbType.Int);
            pr[0].Value = tg.Ma_TG;
            pr[1] = new SqlParameter("@TenTG", SqlDbType.NVarChar);
            pr[1].Value = tg.TenTG;
            sqlHelper.ExecuteNonQuery("update TON_GIAO set TenTG=@TenTG where Ma_TG=@Ma_TG",pr);
        }
        //Định nghĩa hàm xóa 1 tôn giáo
        public void deleteTG(TON_GIAO tg)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TG", SqlDbType.Int);
            pr[0].Value = tg.Ma_TG;
            sqlHelper.ExecuteNonQuery("delete TON_GIAO where Ma_TG=@Ma_TG",pr);
        }

        //Định nghĩa hàm lấy danh sách đơn vị hành chính......................................................................................
        public List<DONVI_HANHCHINH> getDVHC()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from DONVI_HANHCHINH");
            List<DONVI_HANHCHINH> ListDVHC = new List<DONVI_HANHCHINH>();
            foreach (DataRow dr in dt.Rows)
            {
                DONVI_HANHCHINH dvhc = new DONVI_HANHCHINH();
                dvhc.Ma_DV_HC = Convert.ToString(dr[0]);
                dvhc.TenDVHC = dr[1].ToString();
                ListDVHC.Add(dvhc);
            }
            return ListDVHC;
        }
        // Định nghĩa hàm thêm 1 đơn vị hành chính
        public int addDVHC(DONVI_HANHCHINH dvhc)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = dvhc.Ma_DV_HC;
            pr[1] = new SqlParameter("@TenDVHC", SqlDbType.NVarChar);
            pr[1].Value = dvhc.TenDVHC;
            kq = sqlHelper.ExecuteNonQuery("insert DONVI_HANHCHINH(Ma_DV_HC, TenDVHC) values (@ma_DV_HC, @tenDVHC)", pr);
            return kq;
        }
        //Định nghĩa hàm sửa 1 đơn vị hành chính
        public void setDVHC(DONVI_HANHCHINH dvhc)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = dvhc.Ma_DV_HC;
            pr[1] = new SqlParameter("@TenDVHC", SqlDbType.NVarChar);
            pr[1].Value = dvhc.TenDVHC;
            sqlHelper.ExecuteNonQuery("update DONVI_HANHCHINH set TenDVHC=@TenDVHC where Ma_DV_HC=@Ma_DV_HC", pr);
        }
        //Định nghĩa hàm xóa 1 đơn vị hành chính
        public void deleteDVHC(DONVI_HANHCHINH dvhc)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = dvhc.Ma_DV_HC;
            sqlHelper.ExecuteNonQuery("delete DONVI_HANHCHINH where Ma_DV_HC=@Ma_DV_HC", pr);
        }
        //Định nghĩa hàm lấy danh sách dân tộc................................................................................................
        public List<DAN_TOC> getDT()
        {
            DataTable DT = new DataTable();
            sqlHelper.Fill(DT, "select * from DAN_TOC");
            List<DAN_TOC> ListDT = new List<DAN_TOC>();
            foreach (DataRow dr in DT.Rows)
            {
                DAN_TOC dt = new DAN_TOC();
                dt.Ma_DT = Convert.ToInt32(dr[0]);
                dt.TenDT = dr[1].ToString();
                ListDT.Add(dt);
            }
            return ListDT;
        }
        //Định nghĩa hàm thêm 1 dân tộc
        public int addDT(DAN_TOC dt)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DT", SqlDbType.Int);
            pr[0].Value = dt.Ma_DT;
            pr[1] = new SqlParameter("@TenDT", SqlDbType.NVarChar);
            pr[1].Value = dt.TenDT;
            kq = sqlHelper.ExecuteNonQuery("insert DAN_TOC(Ma_DT, TenDT) values (@Ma_DT, @TenDT)", pr);
            return kq;
        }
        // Định nghĩa hàm sửa 1 dân tộc
        public void setDT(DAN_TOC dt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_DT", SqlDbType.Char);
            pr[0].Value = dt.Ma_DT;
            pr[1] = new SqlParameter("@TenDT", SqlDbType.NVarChar);
            pr[1].Value = dt.TenDT;
            sqlHelper.ExecuteNonQuery("update DAN_TOC set TenDT=@TenDT where Ma_DT=@Ma_DT", pr);
        }
        // Định nghĩa hàm xóa một dân tộc
        public void deleteDT(DAN_TOC dt)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DT", SqlDbType.Char);
            pr[0].Value = dt.Ma_DT;
            sqlHelper.ExecuteNonQuery("delete DAN_TOC where Ma_DT=@Ma_DT", pr);
        }

        //Định nghĩa hàm lấy danh sách TIN_HOC
        public List<TIN_HOC> getTH()
        {
            DataTable TH = new DataTable();
            sqlHelper.Fill(TH,"select * from TIN_HOC");
            List<TIN_HOC> ListTH = new List<TIN_HOC>();
            foreach(DataRow dr in TH.Rows)
            {
                TIN_HOC th = new TIN_HOC();
                th.Ma_TH = Convert.ToString(dr[0]);
                th.DienGiai = dr[1].ToString();
                ListTH.Add(th);
            }
            return ListTH;
        }

        //Định nghĩa hàm thêm một trình độ TIN_HOC
        public int addTH(TIN_HOC th)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TH",SqlDbType.Char);
            pr[0].Value = th.Ma_TH;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = th.DienGiai;
            kq = sqlHelper.ExecuteNonQuery("insert TIN_HOC(Ma_TH, DienGiai) values(@Ma_TH, @DienGiai)",pr);
            return kq;
        }
        //Định nghĩa hàm sửa một trình độ TIN_HOC
        public void setTH(TIN_HOC th)
        {
          SqlParameter[] pr = new SqlParameter[2];
          pr[0] = new SqlParameter("@Ma_TH", SqlDbType.Char);
          pr[0].Value = th.Ma_TH;
          pr[1] = new SqlParameter("@DienGiai",SqlDbType.NVarChar);
          pr[1].Value = th.DienGiai;
          sqlHelper.ExecuteNonQuery("update TIN_HOC set DienGiai=@DienGiai where Ma_TH=@Ma_TH ",pr);
        }
        //Định nghĩa hàm xóa một trình độ TIN_HOC
        public void deleteTH(TIN_HOC th)
        {
         SqlParameter[] pr = new SqlParameter[1];
         pr[0] = new SqlParameter("@Ma_TH", SqlDbType.Char);
         pr[0].Value = th.Ma_TH;
         sqlHelper.ExecuteNonQuery("delete TIN_HOC where Ma_TH=@Ma_TH",pr);
        }
        //Kiểm tra mã TH

        //Định nghĩa hàm lấy danh sách NGOAI_NGU
        public List<NGOAI_NGU> getNN()
        {
           DataTable NN = new DataTable();
            sqlHelper.Fill(NN,"select * from NGOAI_NGU");
            List<NGOAI_NGU> ListNN = new List<NGOAI_NGU>();
            foreach (DataRow dr in NN.Rows)
            {
                NGOAI_NGU nn = new NGOAI_NGU();
                nn.Ma_NN = Convert.ToString(dr[0]);
                nn.TenNN = dr[1].ToString();
                ListNN.Add(nn);
            }
            return ListNN;
        }
        //Định nghĩa hàm thêm một NGOAI_NGU
        public int addNN(NGOAI_NGU nn)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_NN",SqlDbType.Char);
            pr[0].Value = nn.Ma_NN;
            pr[1] = new SqlParameter("@TenNN", SqlDbType.NVarChar);
            pr[1].Value = nn.TenNN;
            kq = sqlHelper.ExecuteNonQuery("insert NGOAI_NGU(Ma_NN, TenNN) values (@Ma_NN, @TenNN)",pr);
            return kq;
        }
        //Định nghĩa hàm sửa một NGOAI_NGU
        public void setNN(NGOAI_NGU nn)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_NN",SqlDbType.Char);
            pr[0].Value = nn.Ma_NN;
            pr[1] = new SqlParameter("@TenNN",SqlDbType.NVarChar);
            pr[1].Value = nn.TenNN;
            sqlHelper.ExecuteNonQuery("update NGOAI_NGU set TenNN=@TenNN where Ma_NN=@Ma_NN",pr);
        }
        //Định nghĩa hàm xóa một NGOAI_NGU
        public void deleteNN(NGOAI_NGU nn)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("Ma_NN", SqlDbType.Char);
            pr[0].Value = nn.Ma_NN;
            sqlHelper.ExecuteNonQuery("delete NGOAI_NGU where Ma_NN=@Ma_NN",pr);
        }

        //.......................................TRINHDO_NGOAINGU...........................................///////////////////////////

        public List<TRINHDO_NGOAINGU> getTDNN()
        {
            DataTable TDNN = new DataTable();
            sqlHelper.Fill(TDNN, "select * from TRINHDO_NGOAINGU");
            List<TRINHDO_NGOAINGU> ListTDNN = new List<TRINHDO_NGOAINGU>();
            foreach (DataRow dr in TDNN.Rows)
            {
                TRINHDO_NGOAINGU tdnn = new TRINHDO_NGOAINGU();
                tdnn.Ma_TDNN = Convert.ToString(dr[0]);
                tdnn.TenTDNN = dr[1].ToString();
                ListTDNN.Add(tdnn);
            }
            return ListTDNN;
           
        }

        
        public int addTDNN(TRINHDO_NGOAINGU tdnn)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TDNN",SqlDbType.Char);
            pr[0].Value = tdnn.Ma_TDNN;
            pr[1] = new SqlParameter("@TenTDNN",SqlDbType.NVarChar);
            pr[1].Value = tdnn.TenTDNN;
            kq = sqlHelper.ExecuteNonQuery("insert TRINHDO_NGOAINGU(Ma_TDNN,TenTDNN) values(@Ma_TDNN, @TenTDNN)",pr);
            return kq;
        }

        public void setTDNN(TRINHDO_NGOAINGU tdnn)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TDNN", SqlDbType.Char);
            pr[0].Value = tdnn.Ma_TDNN;
            pr[1] = new SqlParameter("@TenTDNN", SqlDbType.NVarChar);
            pr[1].Value = tdnn.TenTDNN;
            sqlHelper.ExecuteNonQuery("update TRINHDO_NGOAINGU set TenTDNN=@TenTDNN where Ma_TDNN=@Ma_TDNN",pr);
        }

        public void deleteTDNN(TRINHDO_NGOAINGU tdnn)
        {
            SqlParameter[] pr =new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TDNN", SqlDbType.Char);
            pr[0].Value = tdnn.Ma_TDNN;
            sqlHelper.ExecuteNonQuery("delete TRINHDO_NGOAINGU where Ma_TDNN=@Ma_TDNN",pr);
        }

        //........................................TRINHDO_CHINHTRI........................................................
        public List<TRINHDO_CHINHTRI> getTDCT()
        {
            DataTable TDCT = new DataTable();
            sqlHelper.Fill(TDCT,"select * from TRINHDO_CHINHTRI");
            List<TRINHDO_CHINHTRI> ListTDCT = new List<TRINHDO_CHINHTRI>();
            foreach (DataRow dr in TDCT.Rows)
            {
                TRINHDO_CHINHTRI tdct = new TRINHDO_CHINHTRI();
                tdct.Stt_TD_CT = Convert.ToInt32(dr[0]);
                tdct.TenTDCT = dr[1].ToString();
                ListTDCT.Add(tdct);
            }
            return ListTDCT;
        }

        public int addTDCT(TRINHDO_CHINHTRI tdct)
        {
            int kq; 
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Stt_TD_CT", SqlDbType.Int);
            pr[0].Value = tdct.Stt_TD_CT;
            pr[1] = new SqlParameter("@TenTDCT", SqlDbType.NVarChar);
            pr[1].Value = tdct.TenTDCT;
            kq = sqlHelper.ExecuteNonQuery("insert TRINHDO_CHINHTRI(Stt_TD_CT, TenTDCT) values(@Stt_TD_CT, @TenTDCT)",pr);
            return kq;
            
        }

        public void setTDCT(TRINHDO_CHINHTRI tdct)
        {
            
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Stt_TD_CT",SqlDbType.Int);
            pr[0].Value = tdct.Stt_TD_CT;
            pr[1] = new SqlParameter("@TenTDCT",SqlDbType.NVarChar);
            pr[1].Value = tdct.TenTDCT;
            sqlHelper.ExecuteNonQuery("update TRINHDO_CHINHTRI set TenTDCT=@TenTDCT where Stt_TD_CT=@Stt_TD_CT",pr);
            
        }

        public void deleteTDCT(TRINHDO_CHINHTRI tdct)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Stt_TD_CT",SqlDbType.Int);
            pr[0].Value = tdct.Stt_TD_CT;
            sqlHelper.ExecuteNonQuery("delete TRINHDO_CHINHTRI where Stt_TD_CT=@Stt_TD_CT",pr);
        }

        //..............................................TD_QUANLY_NHANUOC.........................................................
        public List<TD_QUANLY_NHANUOC> getTDQLNN()
        {
            DataTable TDQLNN = new DataTable();
            sqlHelper.Fill(TDQLNN, "select * from TD_QUANLY_NHANUOC");
            List<TD_QUANLY_NHANUOC> ListTDQLNN = new List<TD_QUANLY_NHANUOC>();
            foreach(DataRow dr in TDQLNN.Rows )
            {
                TD_QUANLY_NHANUOC tdqlnn = new TD_QUANLY_NHANUOC();
                tdqlnn.Ma_TDQLNN = Convert.ToString(dr[0]);
                tdqlnn.TenTDQLNN = dr[1].ToString();
                ListTDQLNN.Add(tdqlnn);
            }
            return ListTDQLNN;
        }

        public int addTDQLNN(TD_QUANLY_NHANUOC tdqlnn)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TDQLNN",SqlDbType.Char);
            pr[0].Value = tdqlnn.Ma_TDQLNN;
            pr[1] = new SqlParameter("@TenTDQLNN",SqlDbType.NVarChar);
            pr[1].Value = tdqlnn.TenTDQLNN;
            kq = sqlHelper.ExecuteNonQuery("insert TD_QUANLY_NHANUOC(Ma_TDQLNN, TenTDQLNN) values(@Ma_TDQLNN, @TenTDQLNN)",pr);
            return kq;
        }

        public void setTDQLNN(TD_QUANLY_NHANUOC tdqlnn)
        {
            SqlParameter[] pr =new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TDQLNN", SqlDbType.Char);
            pr[0].Value = tdqlnn.Ma_TDQLNN;
            pr[1] = new SqlParameter("@TenTDQLNN", SqlDbType.NVarChar);
            pr[1].Value = tdqlnn.TenTDQLNN;
            sqlHelper.ExecuteNonQuery("update TD_QUANLY_NHANUOC set TenTDQLNN=@TenTDQLNN where Ma_TDQLNN=@Ma_TDQLNN",pr);
        }

        public void deleteTDQLNN(TD_QUANLY_NHANUOC tdqlnn)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TDQLNN", SqlDbType.Char);
            pr[0].Value = tdqlnn.Ma_TDQLNN;
            sqlHelper.ExecuteNonQuery("delete TD_QUANLY_NHANUOC where Ma_TDQLNN=@Ma_TDQLNN", pr);
        }

       //.....................................TRINHDO_CHUYENMON...............................................................
        public List<TRINHDO_CHUYENMON> getTDCM()
        {
            DataTable TDCM = new DataTable();
            sqlHelper.Fill(TDCM,"select * from TRINHDO_CHUYENMON");
            List<TRINHDO_CHUYENMON> ListTDCM = new List<TRINHDO_CHUYENMON>();
            foreach (DataRow dr in TDCM.Rows)
            {
                TRINHDO_CHUYENMON tdcm = new TRINHDO_CHUYENMON();
                tdcm.Ma_TD_CM = Convert.ToString(dr[0]);
                tdcm.TenTDCM = dr[1].ToString();
                ListTDCM.Add(tdcm);
            }
            return ListTDCM;
        }

        public int addTDCM(TRINHDO_CHUYENMON tdcm)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TD_CM",SqlDbType.Char);
            pr[0].Value = tdcm.Ma_TD_CM;
            pr[1] = new SqlParameter("@TenTDCM", SqlDbType.NVarChar);
            pr[1].Value = tdcm.TenTDCM;
            kq = sqlHelper.ExecuteNonQuery("insert TRINHDO_CHUYENMON(Ma_TD_CM, TenTDCM) values(@Ma_TD_CM, @TenTDCM)",pr);
            return kq;
        }

        public void setTDCM(TRINHDO_CHUYENMON tdcm)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TD_CM", SqlDbType.Char);
            pr[0].Value = tdcm.Ma_TD_CM;
            pr[1] = new SqlParameter("@TenTDCM", SqlDbType.NVarChar);
            pr[1].Value = tdcm.TenTDCM;
            sqlHelper.ExecuteNonQuery("update TRINHDO_CHUYENMON set TenTDCM=@TenTDCM where Ma_TD_CM=@Ma_TD_CM",pr);
        }

        public void deleteTDCM(TRINHDO_CHUYENMON tdcm)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TD_CM", SqlDbType.Char);
            pr[0].Value = tdcm.Ma_TD_CM;
            sqlHelper.ExecuteNonQuery("delete TRINHDO_CHUYENMON where Ma_TD_CM=@Ma_TD_CM", pr);
        }

        //.....................................TD_PHOTHONG...............................................................
        public List<TD_PHOTHONG> getTDPT()
        {
            DataTable TDPT = new DataTable();
            sqlHelper.Fill(TDPT,"select * from TD_PHOTHONG");
            List<TD_PHOTHONG> ListTDPT = new List<TD_PHOTHONG>();
            foreach(DataRow dr in TDPT.Rows)
            {
                TD_PHOTHONG tdpt = new TD_PHOTHONG();
                tdpt.Ma_TDPT = Convert.ToString(dr[0]);
                tdpt.TenTDPT = dr[1].ToString();
                ListTDPT.Add(tdpt);
            }
            return ListTDPT;
        }

        public int addTDPT(TD_PHOTHONG tdpt)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[0].Value = tdpt.Ma_TDPT;
            pr[1] = new SqlParameter("@TenTDPT",SqlDbType.NVarChar);
            pr[1].Value = tdpt.TenTDPT;
            kq = sqlHelper.ExecuteNonQuery("insert TD_PHOTHONG(Ma_TDPT, TenTDPT) values(@Ma_TDPT, @TenTDPT)",pr);
            return kq;
        }

        public void setTDPT(TD_PHOTHONG tdpt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[0].Value = tdpt.Ma_TDPT;
            pr[1] = new SqlParameter("@TenTDPT", SqlDbType.NVarChar);
            pr[1].Value = tdpt.TenTDPT;
            sqlHelper.ExecuteNonQuery("update TD_PHOTHONG set TenTDPT=@TenTDPT where Ma_TDPT=@Ma_TDPT",pr);
        }

        public void deleteTDPT(TD_PHOTHONG tdpt)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[0].Value = tdpt.Ma_TDPT;
            sqlHelper.ExecuteNonQuery("delete TD_PHOTHONG where Ma_TDPT=@Ma_TDPT",pr);
        }

        //GD_CHINSACH.........................................................................................................
        public List<GD_CHINHSACH> getGDCS()
        {
            DataTable GDCS = new DataTable();
            sqlHelper.Fill(GDCS, "select * from GD_CHINHSACH");
            List<GD_CHINHSACH> ListGDCS = new List<GD_CHINHSACH>();
            foreach(DataRow dr in GDCS.Rows)
            {
                GD_CHINHSACH gdcs = new GD_CHINHSACH();
                gdcs.Stt_GD_CS = Convert.ToInt32(dr[0]);
                gdcs.DienGiai = dr[1].ToString();
                ListGDCS.Add(gdcs);
            }
            return ListGDCS;
        }

        public int addGDCS(GD_CHINHSACH gdcs)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Stt_GD_CS", SqlDbType.Int);
            pr[0].Value = gdcs.Stt_GD_CS;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = gdcs.DienGiai;
            kq = sqlHelper.ExecuteNonQuery("insert GD_CHINHSACH(Stt_GD_CS, DienGiai) values(@Stt_GD_CS, @DienGiai)", pr);
            return kq;
        }

        public void setGDCS(GD_CHINHSACH gdcs)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Stt_GD_CS", SqlDbType.Int);
            pr[0].Value = gdcs.Stt_GD_CS;
            pr[1] = new SqlParameter("@DienGiai", SqlDbType.NVarChar);
            pr[1].Value = gdcs.DienGiai;
            sqlHelper.ExecuteNonQuery("update GD_CHINHSACH set DienGiai=@DienGiai where Stt_GD_CS=@Stt_GD_CS", pr);
        }

        public void deleteGDCS(GD_CHINHSACH gdcs)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Stt_GD_CS", SqlDbType.Int);
            pr[0].Value = gdcs.Stt_GD_CS;
            sqlHelper.ExecuteNonQuery("delete GD_CHINHSACH where Stt_GD_CS=@Stt_GD_CS", pr);
        }

        //HOSO_DANG.........................................................................................................
        public List<HOSO_DANG> getHSD()
        {
            DataTable HSD = new DataTable();
            sqlHelper.Fill(HSD,"select * from HOSO_DANG");
            List<HOSO_DANG> ListHSD = new List<HOSO_DANG>();
            foreach(DataRow dr in HSD.Rows)
            {
                HOSO_DANG hsd = new HOSO_DANG();
                hsd.Ma_HSD = Convert.ToString(dr[0]);
                hsd.NgayVaoL1 = Convert.ToDateTime(dr[1]);
                hsd.NguoiGioiThieu1 = dr[2].ToString();
                hsd.NgayVaoL2 = Convert.ToDateTime(dr[3]);
                hsd.NguoiGioiThieu2 = dr[4].ToString();
                hsd.NgayChinhThuc1 = Convert.ToDateTime(dr[5]);
                hsd.NgayChinhThuc2 = Convert.ToDateTime(dr[6]);
                hsd.ChiBo = dr[7].ToString();
                hsd.Ma_CB = Convert.ToString(dr[8]);
                ListHSD.Add(hsd);
            }
            return ListHSD;
        }

        public int addHSD(HOSO_DANG hsd)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[9];
            pr[0] = new SqlParameter("@Ma_HSD", SqlDbType.Char);
            pr[0].Value = hsd.Ma_HSD;
            pr[1] = new SqlParameter("@NgayVaoL1", SqlDbType.DateTime);
            pr[1].Value = hsd.NgayVaoL1;
            pr[2] = new SqlParameter("@NguoiGioiThieu1", SqlDbType.NVarChar);
            pr[2].Value = hsd.NguoiGioiThieu1;
            pr[3] = new SqlParameter("@NgayVaoL2", SqlDbType.DateTime);
            pr[3].Value = hsd.NgayVaoL2;
            pr[4] = new SqlParameter("@NguoiGioiThieu2", SqlDbType.NVarChar);
            pr[4].Value = hsd.NguoiGioiThieu2;
            pr[5] = new SqlParameter("@NgayChinhThuc1", SqlDbType.DateTime);
            pr[5].Value = hsd.NgayChinhThuc1;
            pr[6] = new SqlParameter("@NgayChinhThuc2", SqlDbType.DateTime);
            pr[6].Value = hsd.NgayChinhThuc2;
            pr[7] = new SqlParameter("@ChiBo", SqlDbType.NVarChar);
            pr[7].Value = hsd.ChiBo;
            pr[8] = new SqlParameter("Ma_CB", SqlDbType.Char);
            pr[8].Value = hsd.Ma_CB;
            kq = sqlHelper.ExecuteNonQuery("insert HOSO_DANG(Ma_HSD, NgayVaoL1, NguoiGioiThieu1, NgayVaoL2, NguoiGioiThieu2, NgayChinhThuc1, NgayChinhThuc2, ChiBo, Ma_CB) values (@Ma_HSD, @NgayVaoL1, @NguoiGioiThieu1, @NgayVaoL2, @NguoiGioiThieu2, @NgayChinhThuc1, @NgayChinhThuc2, @ChiBo, @Ma_CB)", pr);
            return kq;
        }

        public void setHSD(HOSO_DANG hsd)
        {
            SqlParameter[] pr = new SqlParameter[9];
            pr[0] = new SqlParameter("@Ma_HSD", SqlDbType.Char);
            pr[0].Value = hsd.Ma_HSD;
            pr[1] = new SqlParameter("@NgayVaoL1", SqlDbType.DateTime);
            pr[1].Value = hsd.NgayVaoL1;
            pr[2] = new SqlParameter("@NguoiGioiThieu1", SqlDbType.NVarChar);
            pr[2].Value = hsd.NguoiGioiThieu1;
            pr[3] = new SqlParameter("@NgayVaoL2", SqlDbType.DateTime);
            pr[3].Value = hsd.NgayVaoL2;
            pr[4] = new SqlParameter("@NguoiGioiThieu2", SqlDbType.NVarChar);
            pr[4].Value = hsd.NguoiGioiThieu2;
            pr[5] = new SqlParameter("@NgayChinhThuc1", SqlDbType.DateTime);
            pr[5].Value = hsd.NgayChinhThuc1;
            pr[6] = new SqlParameter("@NgayChinhThuc2", SqlDbType.DateTime);
            pr[6].Value = hsd.NgayChinhThuc2;
            pr[7] = new SqlParameter("@ChiBo", SqlDbType.NVarChar);
            pr[7].Value = hsd.ChiBo;
            pr[8] = new SqlParameter("Ma_CB", SqlDbType.Char);
            pr[8].Value = hsd.Ma_CB;
            sqlHelper.ExecuteNonQuery("update HOSO_DANG set NgayVaoL1=@NgayVaoL1, NguoiGioiThieu1=@NguoiGioiThieu1, NgayVaoL2=@NgayVaoL2, NguoiGioiThieu2=@NguoiGioiThieu2, NgayChinhThuc1=@NgayChinhThuc1, NgayChinhThuc2=@NgayChinhThuc2, ChiBo=@ChiBo, Ma_CB=@Ma_CB where Ma_HSD=@Ma_HSD", pr);
        }

        public void deleteHSD(HOSO_DANG hsd)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_HSD", SqlDbType.Char);
            pr[0].Value = hsd.Ma_HSD;
            sqlHelper.ExecuteNonQuery("delete HOSO_DANG where Ma_HSD=@Ma_HSD",pr);
        }
        //CANBO_TRINHDO_NN.........................................................................................................
        public List<CANBO_TRINHDO_NN> getCB_TDNN()
        {
            DataTable CBTDNN = new DataTable();
            sqlHelper.Fill(CBTDNN,"select * from CANBO_TRINHDO_NN");
            List<CANBO_TRINHDO_NN> ListCBTDNN = new List<CANBO_TRINHDO_NN>();
            foreach(DataRow dr in CBTDNN.Rows)
            {
                CANBO_TRINHDO_NN cbtdnn = new CANBO_TRINHDO_NN();
                cbtdnn.NgayCap = Convert.ToDateTime(dr[0]);
                cbtdnn.NoiCap = dr[1].ToString();
                cbtdnn.XepLoai = dr[2].ToString();
                cbtdnn.Ma_CB = Convert.ToString(dr[3]);
                cbtdnn.Ma_TDNN = Convert.ToString(dr[4]);
                cbtdnn.Ma_NN = Convert.ToString(dr[5]);
                ListCBTDNN.Add(cbtdnn);
            }
            return ListCBTDNN;
        }

        public int addCB_TDNN(CANBO_TRINHDO_NN cbtdnn)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@NgayCap", SqlDbType.DateTime);
            pr[0].Value = cbtdnn.NgayCap;
            pr[1] = new SqlParameter("@NoiCap", SqlDbType.NVarChar);
            pr[1].Value = cbtdnn.NoiCap;
            pr[2] = new SqlParameter("@XepLoai", SqlDbType.NVarChar);
            pr[2].Value = cbtdnn.XepLoai;
            pr[3] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[3].Value = cbtdnn.Ma_CB;
            pr[4] = new SqlParameter("@Ma_TDNN", SqlDbType.Char);
            pr[4].Value = cbtdnn.Ma_TDNN;
            pr[5] = new SqlParameter("@Ma_NN", SqlDbType.Char);
            pr[5].Value = cbtdnn.Ma_NN;
            kq = sqlHelper.ExecuteNonQuery("insert CANBO_TRINHDO_NN(NgayCap, NoiCap, XepLoai, Ma_CB,Ma_TDNN, Ma_NN) values(@NgayCap, @NoiCap, @XepLoai, @Ma_CB, @Ma_TDNN, @Ma_NN)", pr);
            return kq;
        }

        public void setCB_TDNN(CANBO_TRINHDO_NN cbtdnn)
        {
            SqlParameter[] pr = new SqlParameter[6];
            pr[0] = new SqlParameter("@NgayCap", SqlDbType.DateTime);
            pr[0].Value = cbtdnn.NgayCap;
            pr[1] = new SqlParameter("@NoiCap", SqlDbType.NVarChar);
            pr[1].Value = cbtdnn.NoiCap;
            pr[2] = new SqlParameter("@XepLoai", SqlDbType.NVarChar);
            pr[2].Value = cbtdnn.XepLoai;
            pr[3] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[3].Value = cbtdnn.Ma_CB;
            pr[4] = new SqlParameter("@Ma_TDNN", SqlDbType.Char);
            pr[4].Value = cbtdnn.Ma_TDNN;
            pr[5] = new SqlParameter("@Ma_NN", SqlDbType.Char);
            pr[5].Value = cbtdnn.Ma_NN;
            sqlHelper.ExecuteNonQuery("update CANBO_TRINHDO_NN set NgayCap=@NgayCap, NoiCap=@NoiCap, XepLoai=@XepLoai where Ma_CB=@Ma_CB and Ma_TDNN=@Ma_TDNN and Ma_NN=@Ma_NN", pr);
        }

        public void deleteCB_TDNN(CANBO_TRINHDO_NN cbtdnn)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdnn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TDNN", SqlDbType.Char);
            pr[1].Value = cbtdnn.Ma_TDNN;
            pr[2] = new SqlParameter("@Ma_NN", SqlDbType.Char);
            pr[2].Value = cbtdnn.Ma_NN;
            sqlHelper.ExecuteNonQuery("delete CANBO_TRINHDO_NN where Ma_CB=@Ma_CB and Ma_TDNN=@Ma_TDNN and Ma_NN=@Ma_NN", pr);
        }

        //CANBO_TINHOC.............................................................................................................
        public List<CANBO_TINHOC> getCB_TH()
        {
            DataTable CBTH = new DataTable();
            sqlHelper.Fill(CBTH, "select * from CANBO_TINHOC");
            List<CANBO_TINHOC> ListCBTH = new List<CANBO_TINHOC>();
            foreach (DataRow dr in CBTH.Rows)
            {
                CANBO_TINHOC cbth = new CANBO_TINHOC();
                cbth.NgayCap = Convert.ToDateTime(dr[0]);
                cbth.NoiCap = dr[1].ToString();
                cbth.XepLoai = dr[2].ToString();
                cbth.Ma_CB = Convert.ToString(dr[3]);
                cbth.Ma_TH = Convert.ToString(dr[4]);
                ListCBTH.Add(cbth);
            }
            return ListCBTH;
        }

        public int addCB_TH(CANBO_TINHOC cbth)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@NgayCap", SqlDbType.DateTime);
            pr[0].Value = cbth.NgayCap;
            pr[1] = new SqlParameter("@NoiCap", SqlDbType.NVarChar);
            pr[1].Value = cbth.NoiCap;
            pr[2] = new SqlParameter("@XepLoai", SqlDbType.NVarChar);
            pr[2].Value = cbth.XepLoai;
            pr[3] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[3].Value = cbth.Ma_CB;
            pr[4] = new SqlParameter("@Ma_TH", SqlDbType.Char);
            pr[4].Value = cbth.Ma_TH;
            kq = sqlHelper.ExecuteNonQuery("insert CANBO_TINHOC( NgayCap, NoiCap, XepLoai, Ma_CB,Ma_TH) values(@NgayCap, @NoiCap, @XepLoai, @Ma_CB, @Ma_TH)", pr);
            return kq;
        }

        public void setCB_TH(CANBO_TINHOC cbth)
        {
            SqlParameter[] pr = new SqlParameter[5];
            pr[0] = new SqlParameter("@NgayCap", SqlDbType.DateTime);
            pr[0].Value = cbth.NgayCap;
            pr[1] = new SqlParameter("@NoiCap", SqlDbType.NVarChar);
            pr[1].Value = cbth.NoiCap;
            pr[2] = new SqlParameter("@XepLoai", SqlDbType.NVarChar);
            pr[2].Value = cbth.XepLoai;
            pr[3] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[3].Value = cbth.Ma_CB;
            pr[4] = new SqlParameter("@Ma_TH", SqlDbType.Char);
            pr[4].Value = cbth.Ma_TH;
            sqlHelper.ExecuteNonQuery("update CANBO_TINHOC set NgayCap=@NgayCap, NoiCap=@NoiCap, XepLoai=@XepLoai where Ma_CB=@Ma_CB and Ma_TH=@Ma_TH", pr);
        }

        public void deleteCB_TH(CANBO_TINHOC cbth)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbth.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TH", SqlDbType.Char);
            pr[1].Value = cbth.Ma_TH;
            sqlHelper.ExecuteNonQuery("delete CANBO_TINHOC where Ma_CB=@Ma_CB and Ma_TH=@Ma_TH", pr);
        }
        
        //CANBO_TDCT..............................................................................................................
        public List<CANBO_CHINHTRI> getCB_TDCT()
        {
            DataTable CBTDCT = new DataTable();
            sqlHelper.Fill(CBTDCT, "select * from CANBO_CHINHTRI");
            List<CANBO_CHINHTRI> ListCBTDCT = new List<CANBO_CHINHTRI>();
            foreach (DataRow dr in CBTDCT.Rows)
            {
                CANBO_CHINHTRI cbtdct = new CANBO_CHINHTRI();
                cbtdct.NamDT = Convert.ToDateTime(dr[0]);
                cbtdct.TruongDT = dr[1].ToString();
                cbtdct.Ma_CB = Convert.ToString(dr[2]);
                cbtdct.Stt_TD_CT = Convert.ToInt32(dr[3]);
                ListCBTDCT.Add(cbtdct);
            }
            return ListCBTDCT;
        }

        public int addCB_TDCT(CANBO_CHINHTRI cbtdct)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@NamDT", SqlDbType.DateTime);
            pr[0].Value = cbtdct.NamDT;
            pr[1] = new SqlParameter("@TruongDT", SqlDbType.NVarChar);
            pr[1].Value = cbtdct.TruongDT;
            pr[2] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[2].Value = cbtdct.Ma_CB;
            pr[3] = new SqlParameter("@Stt_TD_CT", SqlDbType.Char);
            pr[3].Value = cbtdct.Stt_TD_CT;
            kq = sqlHelper.ExecuteNonQuery("insert CANBO_CHINHTRI( NamDT, TruongDT, Ma_CB,Stt_TD_CT) values(@NamDT, @TruongDT, @Ma_CB, @Stt_TD_CT)", pr);
            return kq;
        }

        public void setCB_TDCT(CANBO_CHINHTRI cbtdct)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@NamDT", SqlDbType.DateTime);
            pr[0].Value = cbtdct.NamDT;
            pr[1] = new SqlParameter("@TruongDT", SqlDbType.NVarChar);
            pr[1].Value = cbtdct.TruongDT;
            pr[2] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[2].Value = cbtdct.Ma_CB;
            pr[3] = new SqlParameter("@Stt_TD_CT", SqlDbType.Char);
            pr[3].Value = cbtdct.Stt_TD_CT;
            sqlHelper.ExecuteNonQuery("update CANBO_CHINHTRI set NamDT=@NamDT, TruongDT=@TruongDT where Ma_CB=@Ma_CB and Stt_TD_CT=@Stt_TD_CT", pr);
        }

        public void deleteCB_TDCT(CANBO_CHINHTRI cbtdct)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdct.Ma_CB;
            pr[1] = new SqlParameter("@Stt_TD_CT", SqlDbType.Char);
            pr[1].Value = cbtdct.Stt_TD_CT;
            sqlHelper.ExecuteNonQuery("delete CANBO_CHINHTRI where Ma_CB=@Ma_CB and Stt_TD_CT=@Stt_TD_CT", pr);
        }
        //CANBO_TDQLNN............................................................................................................
        public List<CANBO_TDQLNN> getCB_TDQLNN()
        {
            DataTable CBTDQLNN = new DataTable();
            sqlHelper.Fill(CBTDQLNN, "select * from CANBO_TDQLNN");
            List<CANBO_TDQLNN> ListCBTDQLNN = new List<CANBO_TDQLNN>();
            foreach (DataRow dr in CBTDQLNN.Rows)
            {
                CANBO_TDQLNN cbtdqlnn = new CANBO_TDQLNN();
                cbtdqlnn.NamDaoTao = Convert.ToDateTime(dr[0]);
                cbtdqlnn.TruongDaoTao = dr[1].ToString();
                cbtdqlnn.Ma_CB = Convert.ToString(dr[2]);
                cbtdqlnn.Ma_TDQLNN = Convert.ToString(dr[3]);
                ListCBTDQLNN.Add(cbtdqlnn);                
            }
            return ListCBTDQLNN;
        }

        public int addCB_TDQLNN(CANBO_TDQLNN cbtdqlnn)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@NamDaoTao", SqlDbType.DateTime);
            pr[0].Value = cbtdqlnn.NamDaoTao;
            pr[1] = new SqlParameter("@TruongDaoTao", SqlDbType.NVarChar);
            pr[1].Value = cbtdqlnn.TruongDaoTao;
            pr[2] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[2].Value = cbtdqlnn.Ma_CB;
            pr[3] = new SqlParameter("@Ma_TDQLNN", SqlDbType.Char);
            pr[3].Value = cbtdqlnn.Ma_TDQLNN;
            kq = sqlHelper.ExecuteNonQuery("insert CANBO_TDQLNN( NamDaoTao, TruongDaoTao, Ma_CB,Ma_TDQLNN) values(@NamDaoTao, @TruongDaoTao, @Ma_CB, @Ma_TDQLNN)", pr);
            return kq;
        }

        public void setCB_TDQLNN(CANBO_TDQLNN cbtdqlnn)
        {
            SqlParameter[] pr = new SqlParameter[4];
            pr[0] = new SqlParameter("@NamDaoTao", SqlDbType.DateTime);
            pr[0].Value = cbtdqlnn.NamDaoTao;
            pr[1] = new SqlParameter("@TruongDaoTao", SqlDbType.NVarChar);
            pr[1].Value = cbtdqlnn.TruongDaoTao;
            pr[2] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[2].Value = cbtdqlnn.Ma_CB;
            pr[3] = new SqlParameter("@Ma_TDQLNN", SqlDbType.Char);
            pr[3].Value = cbtdqlnn.Ma_TDQLNN;
            sqlHelper.ExecuteNonQuery("update CANBO_TDQLNN set NamDaoTao=@NamDaoTao, TruongDaoTao=@TruongDaoTao where Ma_CB=@Ma_CB and Ma_TDQLNN=@Ma_TDQLNN", pr);
        }

        public void deleteCB_TDQLNN(CANBO_TDQLNN cbtdqlnn)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdqlnn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TDQLNN", SqlDbType.Char);
            pr[1].Value = cbtdqlnn.Ma_TDQLNN;
            sqlHelper.ExecuteNonQuery("delete CANBO_TDQLNN where Ma_CB=@Ma_CB and Ma_TDQLNN=@Ma_TDQLNN", pr);
        }


        public List<CB_TDCM_QUATRINH_DT> getCB_TDCM()
        {
            DataTable CBTDCM = new DataTable();
            sqlHelper.Fill(CBTDCM, "select * from CB_TDCM_QUATRINH_DT");
            List<CB_TDCM_QUATRINH_DT> ListCBTDCM = new List<CB_TDCM_QUATRINH_DT>();
            foreach (DataRow dr in CBTDCM.Rows)
            {
                CB_TDCM_QUATRINH_DT cbtdcm = new CB_TDCM_QUATRINH_DT();
                cbtdcm.Ma_CB = Convert.ToString(dr[0]);
                cbtdcm.Ma_TD_CM = Convert.ToString(dr[1]);
                cbtdcm.TenTruong = dr[2].ToString();
                cbtdcm.ChuyenNganhDT = dr[3].ToString();
                cbtdcm.NamBatDau = Convert.ToDateTime(dr[4]);
                cbtdcm.NamKetThuc = Convert.ToDateTime(dr[5]);
                cbtdcm.HinhThucDT = dr[6].ToString();
                ListCBTDCM.Add(cbtdcm);
            }
            return ListCBTDCM;
        }

        public int addCB_TDCM(CB_TDCM_QUATRINH_DT cbtdcm)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[7];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdcm.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TD_CM", SqlDbType.Char);
            pr[1].Value = cbtdcm.Ma_TD_CM;
            pr[2] = new SqlParameter("@TenTruong", SqlDbType.NVarChar);
            pr[2].Value = cbtdcm.TenTruong;
            pr[3] = new SqlParameter("@ChuyenNganhDT", SqlDbType.NVarChar);
            pr[3].Value = cbtdcm.ChuyenNganhDT;
            pr[4] = new SqlParameter("@NamBatDau", SqlDbType.DateTime);
            pr[4].Value = cbtdcm.NamBatDau;
            pr[5] = new SqlParameter("@NamKetThuc", SqlDbType.DateTime);
            pr[5].Value = cbtdcm.NamKetThuc;
            pr[6] = new SqlParameter("@HinhThucDT", SqlDbType.NVarChar);
            pr[6].Value = cbtdcm.HinhThucDT;
            kq = sqlHelper.ExecuteNonQuery("insert CB_TDCM_QUATRINH_DT(TenTruong, ChuyenNganhDT, NamBatDau, NamKetThuc, HinhThucDT, Ma_CB, Ma_TD_CM) values(@TenTruong, @ChuyenNganhDT, @NamBatDau, @NamKetThuc, @HinhThucDT, @Ma_CB, @Ma_TD_CM ) ", pr);
            return kq;
        }

        public void setCB_TDCM(CB_TDCM_QUATRINH_DT cbtdcm)
        {
            SqlParameter[] pr = new SqlParameter[7];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdcm.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TD_CM", SqlDbType.Char);
            pr[1].Value = cbtdcm.Ma_TD_CM;
            pr[2] = new SqlParameter("@TenTruong", SqlDbType.NVarChar);
            pr[2].Value = cbtdcm.TenTruong;
            pr[3] = new SqlParameter("@ChuyenNganhDT", SqlDbType.NVarChar);
            pr[3].Value = cbtdcm.ChuyenNganhDT;
            pr[4] = new SqlParameter("@NamBatDau", SqlDbType.DateTime);
            pr[4].Value = cbtdcm.NamBatDau;
            pr[5] = new SqlParameter("@NamKetThuc", SqlDbType.DateTime);
            pr[5].Value = cbtdcm.NamKetThuc;
            pr[6] = new SqlParameter("@HinhThucDT", SqlDbType.NVarChar);
            pr[6].Value = cbtdcm.HinhThucDT;
            sqlHelper.ExecuteNonQuery("update CB_TDCM_QUATRINH_DT set TenTruong=@TenTruong, ChuyenNganhDT= @ChuyenNganhDT, NamBatDau=@NamBatDau, NamKetThuc=@NamKetThuc, HinhThucDT=@HinhThucDT where Ma_CB=@Ma_CB and Ma_TD_CM=@Ma_TD_CM", pr);
        }

        public void deleteCB_TDCM(CB_TDCM_QUATRINH_DT cbtdcm)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdcm.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TD_CM", SqlDbType.Char);
            pr[1].Value = cbtdcm.Ma_TD_CM;
            sqlHelper.ExecuteNonQuery("delete CB_TDCM_QUATRINH_DT where Ma_CB=@Ma_CB and Ma_TD_CM=@Ma_TD_CM", pr);
        }


        public List<CANBO_TDPT> getCB_TDPT()
        {
            DataTable CBTDPT = new DataTable();
            sqlHelper.Fill(CBTDPT, "select * from CANBO_TDPT");
            List<CANBO_TDPT> ListCBTDPT = new List<CANBO_TDPT>();
            foreach(DataRow dr in CBTDPT.Rows)
            {
                CANBO_TDPT cbtdpt = new CANBO_TDPT();
                cbtdpt.Ma_CB = Convert.ToString(dr[0]);
                cbtdpt.Ma_TDPT = dr[1].ToString();
                ListCBTDPT.Add(cbtdpt);
            }
            return ListCBTDPT;
        }

        public int addCB_TDPT(CANBO_TDPT cbtdpt)
        {
            int kq;
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdpt.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[1].Value = cbtdpt.Ma_TDPT;
            kq = sqlHelper.ExecuteNonQuery("insert CANBO_TDPT(Ma_CB, Ma_TDPT) values(@Ma_CB, @Ma_TDPT)", pr);
            return kq;
        }
     
        public void setCB_TDPT(CANBO_TDPT cbtdpt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdpt.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[1].Value = cbtdpt.Ma_TDPT;
            sqlHelper.ExecuteNonQuery("update CANBO_TDPT set Ma_TDPT=@Ma_TDPT where Ma_CB=@Ma_CB", pr);
        }

        public void deleteCB_TDPT(CANBO_TDPT cbtdpt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdpt.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[1].Value = cbtdpt.Ma_TDPT;
            sqlHelper.ExecuteNonQuery("delete CANBO_TDPT where Ma_CB=@Ma_CB and Ma_TDPT=@Ma_TDPT", pr);
        }

        //Kiem tra ma tin hoc
        public bool check_math(TIN_HOC th)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TH", SqlDbType.Char);
            pr[0].Value = th.Ma_TH;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from TIN_HOC where Ma_TH=@Ma_TH", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        // Kiểm tra mã ngoại ngữ
        public bool check_mann(NGOAI_NGU nn)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_NN",SqlDbType.Char);
            pr[0].Value = nn.Ma_NN;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from NGOAI_NGU where Ma_NN=@Ma_NN", pr);
            if(dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        // Kiểm tra mã dân tộc
        public bool check_dt(DAN_TOC dt)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DT", SqlDbType.Int);
            pr[0].Value = dt.Ma_DT;
            DataTable dtb = new DataTable();
            sqlHelper.Fill(dtb, "select * from DAN_TOC where Ma_DT=@Ma_DT", pr);
            if (dtb.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã tôn giáo
        public bool check_tg(TON_GIAO tg)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TG", SqlDbType.Int);
            pr[0].Value = tg.Ma_TG;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from TON_GIAO where Ma_TG=@Ma_TG", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã mã đơn vị hành chính
        public bool check_dvhc(DONVI_HANHCHINH dvhc)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_DV_HC", SqlDbType.Char);
            pr[0].Value = dvhc.Ma_DV_HC;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from DONVI_HANHCHINH where Ma_DV_HC=@Ma_DV_HC", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã trình độ ngoại ngữ
        public bool check_matdnn (TRINHDO_NGOAINGU tdnn)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TDNN", SqlDbType.Char);
            pr[0].Value = tdnn.Ma_TDNN;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from TRINHDO_NGOAINGU where Ma_TDNN=@Ma_TDNN", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã trinh độ chính trị
        public bool check_tdct(TRINHDO_CHINHTRI tdct)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Stt_TD_CT", SqlDbType.Int);
            pr[0].Value = tdct.Stt_TD_CT;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from TRINHDO_CHINHTRI where Stt_TD_CT=@Stt_TD_CT", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã trình độ quản lý nhà nước
        public bool check_tdqlnn(TD_QUANLY_NHANUOC tdqlnn)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TDQLNN", SqlDbType.Char);
            pr[0].Value = tdqlnn.Ma_TDQLNN;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from TD_QUANLY_NHANUOC where Ma_TDQLNN=@Ma_TDQLNN", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;

        }
        // Kiểm tra mã trình độ chuyên môn
        public bool check_tdcm(TRINHDO_CHUYENMON tdcm)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TD_CM", SqlDbType.Char);
            pr[0].Value = tdcm.Ma_TD_CM;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from TRINHDO_CHUYENMON where Ma_TD_CM=@Ma_TD_CM", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã trình độ phổ thông
        public bool check_tdpt(TD_PHOTHONG tdpt)
        {
            SqlParameter[] pr = new SqlParameter[1];
            pr[0] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[0].Value = tdpt.Ma_TDPT;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select * from TD_PHOTHONG where Ma_TDPT=@Ma_TDPT",pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
            
        // Kiểm tra mã cán bộ và mã tin học    
        public bool check_CB_TH(CANBO_TINHOC cbth)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbth.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TH", SqlDbType.Char);
            pr[1].Value = cbth.Ma_TH;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt,"select * from CANBO_TINHOC where Ma_CB=@Ma_CB and Ma_TH=@Ma_TH",pr);
            if(dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã cán bộ, mã ngoại ngữ, mã trình độ ngoại ngữ
        public bool checkCB_TDNN(CANBO_TRINHDO_NN cbtdnn)
        {
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdnn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_NN", SqlDbType.Char);
            pr[1].Value = cbtdnn.Ma_NN;
            pr[2] = new SqlParameter("@Ma_TDNN", SqlDbType.Char);
            pr[2].Value = cbtdnn.Ma_TDNN;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from CANBO_TRINHDO_NN where Ma_CB=@Ma_CB and Ma_NN=@Ma_NN and Ma_TDNN=@Ma_TDNN", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        //Kiểm tra mã cán bộ, mã trình độ chuyên môn
        public bool checkCB_TDCM(CB_TDCM_QUATRINH_DT cbtdcm)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdcm.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TD_CM", SqlDbType.Char);
            pr[1].Value = cbtdcm.Ma_TD_CM;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from CB_TDCM_QUATRINH_DT where Ma_CB=@Ma_CB and Ma_TD_CM=@Ma_TD_CM", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã cán bộ và mã trình độ QLNN
        public bool checkCB_TDQLNN(CANBO_TDQLNN cbtdqlnn)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdqlnn.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TDQLNN", SqlDbType.Char);
            pr[1].Value = cbtdqlnn.Ma_TDQLNN;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from CANBO_TDQLNN where Ma_CB=@Ma_CB and Ma_TDQLNN=@Ma_TDQLNN", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }

        // Kiểm tra mã cán bộ và mã TDCT
        public bool checkCB_TDCT(CANBO_CHINHTRI cbtdct)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdct.Ma_CB;
            pr[1] = new SqlParameter("@Stt_TD_CT", SqlDbType.Int);
            pr[1].Value = cbtdct.Stt_TD_CT;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from CANBO_CHINHTRI where Ma_CB=@Ma_CB and Stt_TD_CT=@Stt_TD_CT", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
        // Kiểm tra mã cán bộ, mã TDPT
        public bool checkCB_TDPT(CANBO_TDPT cbtdpt)
        {
            SqlParameter[] pr = new SqlParameter[2];
            pr[0] = new SqlParameter("@Ma_CB", SqlDbType.Char);
            pr[0].Value = cbtdpt.Ma_CB;
            pr[1] = new SqlParameter("@Ma_TDPT", SqlDbType.Char);
            pr[1].Value = cbtdpt.Ma_TDPT;
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from CANBO_TDPT where Ma_CB=@Ma_CB and Ma_TDPT=@Ma_TDPT", pr);
            if (dt.Rows.Count > 0)
                return true;
            else
                return false;
        }
       //CAN_BO..............................................................................................
        public List<CAN_BO> getCB()
        {
            DataTable dt = new DataTable();
            sqlHelper.Fill(dt, "select * from CAN_BO");
            List<CAN_BO> ListCB = new List<CAN_BO>();
            foreach (DataRow dr in dt.Rows)
            {
                CAN_BO cb = new CAN_BO();
                cb.Ma_CB = dr[0].ToString();
                cb.HoTen = dr[1].ToString();
                cb.TenKhac = dr[2].ToString();
                cb.NamSinh = Convert.ToDateTime(dr[3]);
                cb.GioiTinh = dr[4].ToString();
                cb.NoiSinh = dr[5].ToString();
                cb.QueQuan = dr[6].ToString();
                cb.HoKhau = dr[7].ToString();
                cb.Cho_O = dr[8].ToString();
                cb.NgheNghiep = dr[9].ToString();
                cb.NgayTuyenDung = Convert.ToDateTime(dr[10]);
                cb.DangVien = Convert.ToBoolean(dr[11]);
                cb.NhapNgu = Convert.ToBoolean(dr[12]);
                cb.SucKhoe = dr[13].ToString();
                cb.ThuongBinh = dr[14].ToString();
                cb.SoCMND = dr[15].ToString();
                cb.NgayCap = Convert.ToDateTime(dr[16]);
                cb.SoBHXH = dr[17].ToString();
                cb.HinhAnh = dr[18].ToString();
                cb.NoiCap = dr[19].ToString();
                cb.Ma_DT = Convert.ToInt32(dr[20]);
                cb.Ma_TG = Convert.ToInt32(dr[21]);
                cb.Stt_GT_CS = Convert.ToInt32(dr[22]);
                cb.Ma_DV_HC = dr[23].ToString();
                ListCB.Add(cb);

            }
            return ListCB;
        }

        public int addCB(CAN_BO cb)
        {
            throw new NotImplementedException();
        }

        public void setCB(CAN_BO cb)
        {
            throw new NotImplementedException();
        }

        public void deleteCB(CAN_BO cb)
        {
            throw new NotImplementedException();
        }

        public bool check_cb(CAN_BO cb)
        {
            throw new NotImplementedException();
        }


   
    }
}
